#!/usr/bin/env python3
# -*- coding:utf-8 -*-
import json
import datetime
import time
import logging
import sys
import os
import subprocess
import pymysql

from sanic import Sanic, response
import uvloop
import asyncio
import mysqlconn
import opsjwt
import hashlib
import io
from openpyxl import load_workbook
import pandas as pd

# pip3 install Sanic
#生成平台对账明细
def set_platform_data(workcode):
  mysql = mysqlconn.MyPymysqlPool("reportmysql")
  sqlone="INSERT INTO t_accountset_customer_platform (orgid,appcode,brand_code,category_code,`客户名称`,`客户应用名称`,`供应商名称`,`场景名称`,`交易类型`,`流水号`,`交易时间`,`交易金额`,`利润`,`客户收银台立减金额`,`应付客户通道手续费`,`应收客户服务费`,`现金结算金额`,`应付开票成本`,`订单编号`,`手机号码`,`用户ID`,`订单金额`,`订单状态`,`退款金额`,`退款类型`,`供应商侧订单号`,`供应商侧订单金额`,`供应商侧退款金额`,`用户支付金额`,`下单时间`,`优惠券名称`,`优惠券ID`,`优惠券活动结算金额`,`优惠券所属积分金额`) SELECT orgid,appcode,brand_code,category_code,`客户名称`,`客户应用名称`,`供应商名称`,`场景名称`,`交易类型`,`流水号`,`交易时间`,`交易金额`,round(`交易金额`-`应付客户通道手续费`+`应收客户服务费`+`优惠券活动结算金额`-`应付开票成本`-`供应商侧订单金额`,2) AS `利润=现金结算金额+优惠券活动结算金额-应付开票成本-应付供应商金额`,`客户收银台立减金额`,`应付客户通道手续费`,`应收客户服务费`,round(`交易金额`-`应付客户通道手续费`+`应收客户服务费`,2) AS `现金结算金额=交易金额-通道手续费+服务费`,`应付开票成本`,`订单编号`,`手机号码`,`用户ID`,`订单金额`,`订单状态`,`退款金额`,`退款类型`,`供应商侧订单号`,`供应商侧订单金额`,`供应商侧退款金额`,`用户支付金额`,`下单时间`,`优惠券名称`,`优惠券ID`,IF (`优惠券活动结算金额`='',0,`优惠券活动结算金额`),IF (`优惠券所属积分金额`='',0,`优惠券所属积分金额`) FROM (SELECT k.id AS orgid,e.appcode,d.brand_code,c.category_code,k.t_organization_name AS `客户名称`,`a`.`application_name` AS `客户应用名称`,`d`.`brand_name` AS `供应商名称`,`c`.`category_name` AS `场景名称`,'支付' AS '交易类型',`e`.`customer_paymentserial` AS `流水号`,date_format(e.paymenttime,'%Y-%m-%d %H:%i:%s') AS `交易时间`,`e`.`price` AS `交易金额`,`e`.`customerdiscountprice` AS `客户收银台立减金额`,(CASE WHEN (`e`.`price`)*ifnull(f.config_content,0)*ifnull(f.paystatus,0)=0 THEN 0 WHEN (`e`.`price`)*ifnull(f.config_content,0)*ifnull(f.paystatus,0)> 0 AND (`e`.`price`)*ifnull(f.config_content,0)*ifnull(f.paystatus,0)< 0.01 THEN 0.01 ELSE round((`e`.`price`)*ifnull(f.config_content,0)*ifnull(f.paystatus,0),2) END) AS `应付客户通道手续费`,(CASE WHEN (`e`.orderprice)*ifnull(g.config_content,0)*ifnull(g.paystatus,0)=0 THEN 0 WHEN (`e`.`orderprice`)*ifnull(g.config_content,0)*ifnull(g.paystatus,0)> 0 AND (`e`.`orderprice`)*ifnull(g.config_content,0)*ifnull(g.paystatus,0)< 0.01 THEN 0.01 ELSE round((`e`.`orderprice`)*ifnull(g.config_content,0)*ifnull(g.paystatus,0),2) END) AS `应收客户服务费`,(CASE WHEN (`e`.orderprice)*ifnull(h.config_content,0)*ifnull(h.paystatus,0)=0 THEN 0 WHEN (`e`.`orderprice`)*ifnull(h.config_content,0)*ifnull(h.paystatus,0)> 0 AND (`e`.`orderprice`)*ifnull(h.config_content,0)*ifnull(h.paystatus,0)< 0.01 THEN 0.01 ELSE round((`e`.`orderprice`)*ifnull(h.config_content,0)*ifnull(h.paystatus,0),2) END) AS `应付开票成本`,e.orderid AS `订单编号`,e.userphone AS `手机号码`,e.userid AS `用户ID`,`e`.`orderprice` AS `订单金额`,e.displaystatus AS `订单状态`,e.refundedprice AS `退款金额`,(CASE WHEN ifnull(e.refundedprice,0)=0 THEN '' WHEN `e`.`orderprice`-ifnull(e.refundedprice,0)=0 THEN '全额退款' WHEN `e`.`orderprice`> ifnull(e.refundedprice,0) THEN '部分退款' END) AS `退款类型`,e.channel_orderid AS `供应商侧订单号`,e.channel_orderprice AS `供应商侧订单金额`,(CASE WHEN e.STATUS=7 AND e.refundedprice> e.channel_orderprice THEN e.channel_orderprice WHEN e.STATUS=7 AND e.refundedprice<=e.channel_orderprice THEN e.refundedprice END) AS `供应商侧退款金额`,`e`.`price` AS `用户支付金额`,date_format(e.createtime,'%Y-%m-%d %H:%i:%s') AS `下单时间`,e.coupon_title AS `优惠券名称`,e.coupon_id AS `优惠券ID`,e.coupon_value AS `优惠券活动结算金额`,e.pointPrice AS `优惠券所属积分金额` FROM `t_order` `e` LEFT JOIN `t_application` `a` ON `e`.`appcode`=`a`.`application_code` LEFT JOIN `t_category` `c` ON `e`.`ordertype`=`c`.`category_code` LEFT JOIN `t_brand` `d` ON `e`.`channel`=`d`.`brand_code` AND e.ordertype=d.package_name LEFT JOIN `t_customer_account_config` f ON `e`.`appcode`=f.app_code AND e.`ordertype`=f.category_code AND `e`.`channel`=f.`brand_code` AND date_format(e.paymenttime,'%Y-%m-%d')>=f.config_begindate AND date_format(e.paymenttime,'%Y-%m-%d')<=f.config_enddate AND f.config_types='aisle' LEFT JOIN `t_customer_account_config` g ON `e`.`appcode`=g.app_code AND e.`ordertype`=g.category_code AND `e`.`channel`=g.`brand_code` AND date_format(e.paymenttime,'%Y-%m-%d')>=g.config_begindate AND date_format(e.paymenttime,'%Y-%m-%d')<=g.config_enddate AND g.config_types='brokerage' LEFT JOIN `t_customer_account_config` h ON `e`.`appcode`=h.app_code AND e.`ordertype`=h.category_code AND `e`.`channel`=h.`brand_code` AND date_format(e.paymenttime,'%Y-%m-%d')>=h.config_begindate AND date_format(e.paymenttime,'%Y-%m-%d')<=h.config_enddate AND h.config_types='bill' LEFT JOIN t_organization_application j ON a.id=j.application_id LEFT JOIN t_organization k ON j.organization_id=k.id WHERE e.appcode='allinpay' AND `e`.`paymentat`> 0 AND e.paymenttime>='2022-09-01' AND e.paymenttime< '2022-10-01' UNION ALL SELECT k.id AS orgid,e.appcode,d.brand_code,c.category_code,k.t_organization_name AS `客户名称`,`a`.`application_name` AS `客户应用名称`,`d`.`brand_name` AS `供应商名称`,`c`.`category_name` AS `场景名称`,'退款' AS '交易类型',`e`.`customer_fundmentserial` AS `流水号`,date_format(e.refundedtime,'%Y-%m-%d %H:%i:%s') AS `交易时间`,-e.refundedprice AS `交易金额`,-`e`.`customerdiscountprice` AS `客户收银台立减金额`,(CASE WHEN e.refundedprice*ifnull(f.config_content,0)*ifnull(f.refundstatus,0)=0 THEN 0 WHEN e.refundedprice*ifnull(f.config_content,0)*ifnull(f.refundstatus,0)> 0 AND e.refundedprice*ifnull(f.config_content,0)*ifnull(f.refundstatus,0)< 0.01 THEN-0.01 ELSE round(-e.refundedprice*ifnull(f.config_content,0)*ifnull(f.refundstatus,0),2) END) AS `应付客户通道手续费`,(CASE WHEN e.refundedprice*ifnull(g.config_content,0)*ifnull(g.refundstatus,0)=0 THEN 0 WHEN e.refundedprice*ifnull(g.config_content,0)*ifnull(g.refundstatus,0)> 0 AND e.refundedprice*ifnull(g.config_content,0)*ifnull(g.refundstatus,0)< 0.01 THEN-0.01 ELSE round(-e.refundedprice*ifnull(g.config_content,0)*ifnull(g.refundstatus,0),2) END) AS `应收客户服务费`,(CASE WHEN e.refundedprice*ifnull(h.config_content,0)*ifnull(h.refundstatus,0)=0 THEN 0 WHEN e.refundedprice*ifnull(h.config_content,0)*ifnull(h.refundstatus,0)> 0 AND e.refundedprice*ifnull(h.config_content,0)*ifnull(h.refundstatus,0)< 0.01 THEN-0.01 ELSE round(-e.refundedprice*ifnull(h.config_content,0)*ifnull(h.refundstatus,0),2) END) AS `应付开票成本`,e.orderid AS `订单编号`,e.userphone AS `手机号码`,e.userid AS `用户ID`,`e`.`orderprice` AS `订单金额`,e.displaystatus AS `订单状态`,e.refundedprice AS `退款金额`,(CASE WHEN ifnull(e.refundedprice,0)=0 THEN '' WHEN `e`.`orderprice`-ifnull(e.refundedprice,0)=0 THEN '全额退款' WHEN `e`.`orderprice`> ifnull(e.refundedprice,0) THEN '部分退款' END) AS `退款类型`,e.channel_orderid AS `供应商侧订单号`,-e.channel_orderprice AS `供应商侧订单金额`,(CASE WHEN e.STATUS=7 AND e.refundedprice> e.channel_orderprice THEN e.channel_orderprice WHEN e.STATUS=7 AND e.refundedprice<=e.channel_orderprice THEN e.refundedprice END) AS `供应商侧退款金额`,`e`.`price` AS `用户支付金额`,date_format(e.createtime,'%Y-%m-%d %H:%i:%s') AS `下单时间`,-((CASE WHEN `e`.price-e.refundedprice=0 THEN ifnull(e.coupon_value,0) ELSE 0 END)) AS `优惠券活动结算金额`,e.coupon_title AS `优惠券名称`,e.coupon_id AS `优惠券ID`,e.pointPrice AS `优惠券所属积分金额` FROM `t_order` `e` LEFT JOIN `t_application` `a` ON `e`.`appcode`=`a`.`application_code` LEFT JOIN `t_category` `c` ON `e`.`ordertype`=`c`.`category_code` LEFT JOIN `t_brand` `d` ON `e`.`channel`=`d`.`brand_code` AND e.ordertype=d.package_name LEFT JOIN `t_customer_account_config` f ON `e`.`appcode`=f.app_code AND e.`ordertype`=f.category_code AND `e`.`channel`=f.`brand_code` AND date_format(e.refundedtime,'%Y-%m-%d')>=f.config_begindate AND date_format(e.refundedtime,'%Y-%m-%d')<=f.config_enddate AND f.config_types='aisle' LEFT JOIN `t_customer_account_config` g ON `e`.`appcode`=g.app_code AND e.`ordertype`=g.category_code AND `e`.`channel`=g.`brand_code` AND date_format(e.refundedtime,'%Y-%m-%d')>=g.config_begindate AND date_format(e.refundedtime,'%Y-%m-%d')<=g.config_enddate AND g.config_types='brokerage' LEFT JOIN `t_customer_account_config` h ON `e`.`appcode`=h.app_code AND e.`ordertype`=h.category_code AND `e`.`channel`=h.`brand_code` AND date_format(e.paymenttime,'%Y-%m-%d')>=h.config_begindate AND date_format(e.paymenttime,'%Y-%m-%d')<=h.config_enddate AND h.config_types='bill' LEFT JOIN t_organization_application j ON a.id=j.application_id LEFT JOIN t_organization k ON j.organization_id=k.id WHERE e.appcode='allinpay' AND `e`.`paymentat`> 0 AND e.refundedtime>='2022-09-01' AND e.refundedtime< '2022-10-01') aa"
  result = mysql.insert(sqlone)
  mysql.dispose
  return result


#生成客户对账明细
async def upload_excel(request):
  dict_rr={}
  workcode=datetime.datetime.now().strftime("%Y%m%d%H%M%S.%f")
  print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"),workcode,"收到接口请求[/upload_excel]:")
  ip=request.socket[0]
  file = request.files.get('file')  # 获取前端传的文件对象，注意是File对象，并不是我们平时使用的file对象
  bytes_io = io.BytesIO()  # 创建一个二进制io流文件对象，这个对象才可以被load_workbook加载
  bytes_io.write(file.body)  # 将文件数据传写入bytes_io对象中
  wb = load_workbook(bytes_io)  # 这时候就可以加载到workbook对象了
  bytes_io.close()  # 使用完关闭文件
  
	# sheet_names = wb.get_sheet_names()  # 获取所有的sheet名称
  sheet_names = wb.sheetnames  # 获取所有的sheet名称
  sheet_name = sheet_names[0]  # 拿到第一个sheet，根据实际情况来看
	
  sheet = wb[sheet_name]  # 创建工作簿对象
  title_list = sheet.values  # 第一行的行头或者列名
  
  print(sheet_name)
  max_row = sheet.max_row  # 获取最大行数（自动识别有数据的行，如果数据行是连续的）
  max_col = sheet.max_column  # 获取最大列数（自动识别有数据的列，也要数据连续才行）
	
	# 下面遍历每行，拿到数据，第一行为行头，略过
  data = []
  i=j=0
  colname=""
  mysql = mysqlconn.MyPymysqlPool("reportmysql")
  for row in sheet.iter_rows(min_row=1, max_col=max_col, max_row=max_row, values_only=True):
	  # data.append(row)
    #获取第一行作为字段名称
    if(i==0):
      for cell in row:
        if(j==0):
          colname=cell
        else:
          colname=colname+","+cell
        j=j+1
    #第二行开始插入内容
    else:
      sqlone = "INSERT into t_ack_allinpay_0001("+colname+") values(%s,%s,%s,%s);"
      result = mysql.insert(sqlone,row)
    
    
    i=i+1
    
  
  dict_rr["code"]=0

  mysql.dispose()
  #记录业务日志
  # logtime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
  # insertlog(workcode,dict_userinfo["userid"],"loginlog",logtime,"登录系统成功",ip)
    
  dict_rr["success"]="true"
  dict_rr["message"]="上传成功"
  return response.json(dict_rr)
  
  
#返回账单的标题
async def upload_get_excel_head(request):
  dict_rr={}
  workcode=datetime.datetime.now().strftime("%Y%m%d%H%M%S.%f")
  print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"),workcode,"收到接口请求[/upload_excel_head]:")
  ip=request.socket[0]
  file = request.files.get('file')  # 获取前端传的文件对象，注意是File对象，并不是我们平时使用的file对象
  bytes_io = io.BytesIO()  # 创建一个二进制io流文件对象，这个对象才可以被load_workbook加载
  bytes_io.write(file.body)  # 将文件数据传写入bytes_io对象中
  wb = load_workbook(bytes_io)  # 这时候就可以加载到workbook对象了
  bytes_io.close()  # 使用完关闭文件
  
	# sheet_names = wb.get_sheet_names()  # 获取所有的sheet名称
  sheet_names = wb.sheetnames  # 获取所有的sheet名称
  sheet_name = sheet_names[0]  # 拿到第一个sheet，根据实际情况来看
	
  sheet = wb[sheet_name]  # 创建工作簿对象
  title_list = sheet.values  # 第一行的行头或者列名
  
  print(sheet_name)
  max_row = sheet.max_row  # 获取最大行数（自动识别有数据的行，如果数据行是连续的）
  max_col = sheet.max_column  # 获取最大列数（自动识别有数据的列，也要数据连续才行）
	
	# 下面遍历每行，拿到数据，第一行为行头，略过
  list_cell=[]
  
  colname=""
  for row in sheet.iter_rows(min_row=1, max_col=max_col, max_row=max_row, values_only=True):
	  # data.append(row)
    #获取第一行作为字段名称
    for cell in row:
      dict_cell={}
      dict_cell["label"] = cell
      dict_cell["value"] = cell
      list_cell.append(dict_cell)
    break
  
  
  #记录业务日志
  # logtime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
  # insertlog(workcode,dict_userinfo["userid"],"loginlog",logtime,"登录系统成功",ip)

  return response.json(list_cell)  
  
async def insert_books(request):
  dict_rr={}
  workcode=datetime.datetime.now().strftime("%Y%m%d%H%M%S.%f")
  print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f"),workcode,"收到接口请求[/insert_books]:")
  ip=request.socket[0]
  return response.json(dict_rr) 
